package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class ReviewTC
 */
//@WebServlet(description = "檢視送審的測試案例", urlPatterns = { "/ReviewTC" })
public class ReviewTC extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ReviewTC() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		boolean isReviewer = false;
		Widgets wid = new Widgets();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String strSQL = "SELECT COUNT(*) FROM tms_roleusermapping WHERE roleID=3 AND userID =?";
		String loginUser = request.getParameter("loginUser");
		String returnResult = "";
		ArrayList<MRBody> mrBodys = new ArrayList<MRBody>();
		
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
		    ps.setString(1, loginUser);
			rs = ps.executeQuery();
			rs.next();
			isReviewer = rs.getInt(1)==0?false:true;
			
			strSQL = "SELECT t2.tpName,t1.tpid,t1.mrid,t1.sourceid,t1.mrdescription FROM tms_tpitem t1, tms_tp t2 WHERE t1.isTCSubmitted=1 AND t1.isTCReviewed=0 AND t2.tpid=t1.tpid";
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			
			while (rs.next()){
				int tpid = rs.getInt("tpid");
				String mrid =rs.getString("mrid");
				strSQL ="SELECT MAX(ver) FROM tms_mrtcmapping WHERE tpid = ? AND mrid = ?";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				rs2 = ps.executeQuery();
				rs2.next();
				int ver = rs2.getInt(1);
				
				MRBody mrBody = new MRBody();
				mrBody.setTpid(String.valueOf(tpid));
				mrBody.setReviewer(isReviewer);
				mrBody.setTpName(rs.getString("tpName"));
				mrBody.setMrid(mrid);
				mrBody.setMrdescription(rs.getString("mrdescription"));
				mrBody.setSourceid(rs.getString("sourceid"));
				mrBody.setVer(ver);
				mrBody.setTester(erList("tester",tpid,mrid,conn,ps,rs));
				mrBodys.add(mrBody);
			}
			
			returnResult = new Gson().toJson(mrBodys);
			returnResult = returnResult.substring(1, returnResult.length());
			returnResult = "{\"sqlData\":["+returnResult+",\"strSQL\":\""+strSQL+"\",\"size\":\""+mrBodys.size()+"\"}";
		} catch (SQLException e) {
			returnResult = "{\"errorMsg\":\""+ wid.keepErrorMes(conn, ps, e, "", true) +"\"}";
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (rs2!= null) {rs2.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				returnResult = e.toString();
			}
		}
		
		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(returnResult);
	
	}
	
	private User[] erList(String type,int tpid,String mrid,Connection conn,PreparedStatement ps,ResultSet rs) {
		User[] erList2 = null;
		String strSQL = "SELECT COUNT(userID) FROM tms_mritem_"+type+" WHERE tpid=? AND mrid=?";
		
		try {
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			rs = ps.executeQuery();
			
			rs.next();
			int count = rs.getInt(1); 
			if ( count !=0 ) {
				erList2 = new User[count];
				/**
				 * 測試項目的
				 * **/
				strSQL = "SELECT tms_mritem_"+type+".userID, tms_personnel.nickName FROM tms_mritem_"+type+" left join tms_personnel on tms_mritem_"+type+".userID = tms_personnel.userID WHERE tms_mritem_"+type+".tpid=? AND tms_mritem_"+type+".mrid=?";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				rs = ps.executeQuery();
				int ind = 0;
				while (rs.next()){
					erList2[ind] = new User();
					erList2[ind].setUserID(rs.getString("userID"));
					erList2[ind].setNickName(rs.getString("nickName"));
					ind++;
				}
			}
		} catch (SQLException e) {
			Widgets wid = new Widgets();
			wid.keepErrorMes(conn, ps, e, "", true);
		}

		return erList2;
	}
}